﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SQLDB
{
    public class SQLDB
    {
        //连接字符串
        static string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
        //返回DataTable对象
        #region
        public static DataTable GetTable(string sql)
        {
            return GetTable(sql, null);
        }
        public static DataTable GetTable(string sql, SqlParameter[] pas)
        {
            return GetTable(sql, pas, CommandType.Text);

        }
        /// <summary>
        /// 执行查询，返回DataTable对象
        /// </summary>
        /// <param name="strSQL">sql语句</param>
        /// <param name="pas">参数数组</param>
        /// <param name="cmdtype">Command类型</param>
        /// <returns>DataTable对象</returns>
        public static DataTable GetTable(string sql, SqlParameter[] pas, CommandType comdtype)
        {

            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.SelectCommand.CommandType = comdtype;
                if (pas != null)
                {
                    da.SelectCommand.Parameters.AddRange(pas);
                }
                da.Fill(dt);
            }
            return dt;
        }
        #endregion
        //执行非查询存储过程SQL语句
        #region
        public static int ExcuteProc(string ProName)
        {
            return ExcuteSQL(ProName, null, CommandType.StoredProcedure);
        }
        public static int ExcuteProc(string ProName, SqlParameter[] pars)
        {
            return ExcuteSQL(ProName, pars, CommandType.StoredProcedure);
        }

        public static int ExcuteSQL(string sql)
        {
            return ExcuteSQL(sql, null);
        }
        public static int ExcuteSQL(string sql, SqlParameter[] pas)
        {
            return ExcuteSQL(sql, pas, CommandType.Text);
        }

        /// 执行非查询存储过程和SQL语句
        /// 增、删、改
        /// </summary>
        /// <param name="strSQL">要执行的SQL语句</param>
        /// <param name="paras">参数列表，没有参数填入null</param>
        /// <param name="cmdType">Command类型</param>
        /// <returns>返回影响行数</returns>
        public static int ExcuteSQL(string strSQL, SqlParameter[] paras, CommandType comType)
        {
            int i = 0;
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                SqlCommand comd = new SqlCommand(strSQL, conn);
                comd.CommandType = comType;
                if (paras != null)
                {
                    comd.Parameters.AddRange(paras);
                }
                i = comd.ExecuteNonQuery();
                conn.Close();
            }
            return i;
        }
        #endregion
        //返回查询的行数
        #region
        public static int ExcuteScalarSQL(string strSQL)
        {
            return ExcuteScalarSQL(strSQL, null);
        }

        public static int ExcuteScalarSQL(string strSQL, SqlParameter[] paras)
        {
            return ExcuteScalarSQL(strSQL, paras, CommandType.Text);
        }
        public static int ExcuteScalarProc(string strSQL, SqlParameter[] paras)
        {
            return ExcuteScalarSQL(strSQL, paras, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 执行SQL语句，返回第一行，第一列
        /// </summary>
        /// <param name="strSQL">要执行的SQL语句</param>
        /// <param name="paras">参数列表，没有参数填入null</param>
        /// <returns>返回影响行数</returns>
        public static int ExcuteScalarSQL(string strSQL, SqlParameter[] paras, CommandType cmdType)
        {
            int i = 0;
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand(strSQL, conn);
                cmd.CommandType = cmdType;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                conn.Open();
                SqlTransaction tran = conn.BeginTransaction();
                try
                {
                    i = Convert.ToInt32(cmd.ExecuteScalar());
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    throw ex;
                }
                finally
                {
                    conn.Close();

                }

            }
            return i;

        }
        #endregion
        //批量插入
        #region
        /// <summary>
        /// 往数据库中批量插入数据
        /// </summary>
        /// <param name="sourceDt">数据源表</param>
        /// <param name="targetTable">服务器上目标表</param>
        public static void BulkToDB(DataTable sourceDt, string targetTable)
        {

            SqlConnection conn = new SqlConnection(strConn);
            SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
            bulkCopy.DestinationTableName = targetTable; //服务器上目标表的名称
            bulkCopy.BatchSize = sourceDt.Rows.Count;   //每一批次中的行数
            conn.Open();
            SqlTransaction tran = conn.BeginTransaction();
            try
            {
                if (sourceDt != null && sourceDt.Rows.Count != 0)
                {
                    bulkCopy.WriteToServer(sourceDt);
                    tran.Commit();
                }
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw ex;

            }
            finally
            {
                conn.Close();
                if (bulkCopy != null)
                {
                    bulkCopy.Close();
                }
            }
        }
        #endregion

        //获得DataReader数据
        public static SqlDataReader GetReader(string strSql, SqlParameter[] paras, CommandType comType)
        {
            SqlDataReader dr = null;
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand comd = new SqlCommand(strSql, conn);
            comd.CommandType = comType;
            if (paras != null)
            {
                comd.Parameters.AddRange(paras);
            }
            conn.Open();
            dr = comd.ExecuteReader(CommandBehavior.CloseConnection);
            return dr;
        }
    }
}
